package com.siemens.industrial.app;

import org.springframework.jdbc.core.JdbcTemplate;

import javax.mail.MessagingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/11/30.
 */
public class EventForService {
    private static final String getServiceWillEnd = "SELECT a.tenant_id,a.service_id,a.enabled,a.end_date,date(now()) 'now',DATE_SUB(a.end_date, INTERVAL 1 month) bm,DATE_SUB(a.end_date, INTERVAL 1 week) bw FROM siemens.tenant_service_relation a where (date(now()) >=DATE_SUB(a.end_date, INTERVAL 1 month)) and end_date>date(now());";
    private static final String getMailConfig = "select * from `siemens`.system_config where system_key ='mail_config_key'";
    private static final String getCountForSend = "select count(1) from siemens.mail_send_log where type=2 and tenantId=? and serviceId=? and send_time between ? and ?;";
    private static final String getServiceById = "select * from siemens.service where service_id=?";
    private static final String getTenantById = "select * from siemens.tenant where tenant_id = ?";
    private static final String getUserByEventId = "SELECT a.user_id,a.event_id FROM siemens.user_event_relation a where a.event_id=?;";
    private static final String insertMessage = "INSERT INTO `siemens`.`message` (`event_id`, `status`, `executant`, `operands`, `service_id`) VALUES (?, ?, ?, ?, ?);";
    private static final String getMails = "SELECT group_concat(a.email) mails FROM siemens.user_event_relation a where a.event_id=?;";
    private static final String insertMailLog = "INSERT INTO `siemens`.`mail_send_log` (`send_time`, `type`, `tenantId`, `serviceId`, `description`) VALUES (now(), ?, ?, ?, ?);";
    private static final String messageIsExit = "select count(1) from siemens.message where user_id=? and event_id= ? and (create_time between ? and ?) and message =?;";
    private JdbcTemplate jdbcTemplate;
    private static SimpleDateFormat simpleDateFormat;

    public EventForService() {
        simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    }

    public void send() {
        System.out.println("Hello, sql! >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        jdbcTemplate = DBHelper.getJdbcTemplate();

        if (jdbcTemplate == null) {
            System.out.println("Get database connection failed.");
            return;
        }

        List<Map<String, Object>> willEnds = getServiceWillEnd();

        if (willEnds.size() == 0)
            return;

        for (Map<String, Object> map : willEnds) {
            try {
                Date now = simpleDateFormat.parse(map.get("now").toString());
                Date bm = simpleDateFormat.parse(map.get("bm").toString());
                Date bw = simpleDateFormat.parse(map.get("bw").toString());
                Date endDate = simpleDateFormat.parse(map.get("end_date").toString());
                String tenantId = map.get("tenant_id").toString();
                String serviceId = map.get("service_id").toString();

                List<Map<String, Object>> mailConfig = this.getMailConfig();
                if (mailConfig == null || mailConfig.size() == 0)
                    return;
                Map<String, String> pps = SendMail.buildMailProperties(mailConfig.get(0).get("system_value").toString());

                if ((now.after(bm) && now.before(bw)) || now.equals(bm)) {//服务到期一个月
                    if (getCount(tenantId, serviceId, bm, endDate) == 0) {
                        buildMessage(tenantId, serviceId, "3", pps, bm, endDate);
                    }
                } else {//服务到期七天
                    buildMessage(tenantId, serviceId, "4", pps, bw, endDate);
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
    }

    private List<Map<String, Object>> getServiceWillEnd() {
        return jdbcTemplate.queryForList(getServiceWillEnd);
    }

    public List<Map<String, Object>> getMailConfig() {
        return jdbcTemplate.queryForList(getMailConfig);
    }

    private int getCount(String tenantId, String serviceId, Date bm, Date endDate) {
        int i = jdbcTemplate.queryForObject(getCountForSend, new Object[]{tenantId, serviceId, bm, endDate}, Integer.class);
        return i;
    }

    public Map<String, Object> getServiceById(String serviceId) {
        return jdbcTemplate.queryForMap(getServiceById, serviceId);
    }

    public Map<String, Object> getTenantById(String tenantId) {
        return jdbcTemplate.queryForMap(getTenantById, tenantId);
    }

    public List<Map<String, Object>> getUserIdEvent(String eventId) {
        return jdbcTemplate.queryForList(getUserByEventId, eventId);
    }

    public Map<String, Object> getMailsByEvent(String eventId) {
        return jdbcTemplate.queryForMap(getMails, eventId);
    }

    public void sendMessage(String[] mail, Map<String, String> pps, String text) {
        if (mail.length > 0) {
            try {
                SendMail send = new SendMail(pps);
                send.setMailParam("租户服务到期提醒", mail, text, pps.get("mailAddress_key"));
                send.send();
            } catch (MessagingException e) {
                System.out.println("send mail fail");
            }
        }
    }

    private void buildMessage(String tenantId, String serviceId, String eventId, Map<String, String> pps, Date bmw, Date endDate) {
        String name;
        if (eventId.equals("3")) {
            name = "服务还有一个月即将到期";
        } else {
            name = "服务还有一周即将到期";
        }
        String message = "租户" + this.getTenantById(tenantId).get("tenant_name") + "的" + this.getServiceById(serviceId).get("service_name") + name;

        jdbcTemplate.update(insertMessage, eventId, 0, "系统通知", this.getTenantById(tenantId).get("tenant_name"), this.getServiceById(serviceId).get("service_name"));

        Map<String, Object> mails = this.getMailsByEvent(eventId);
        if (mails.get("mails") != null) {
            String[] mail = (mails.get("mails").toString()).split(",");
            this.sendMessage(mail, pps, message);
            insertMailLog(tenantId, serviceId, "服务即将过期");
        }
    }

    private void insertMailLog(String tenantId, String serviceId, String description) {
        jdbcTemplate.update(insertMailLog, 2, tenantId, serviceId, description);
    }

    private int messageIsExit(String userId, String eventId, Date bmw, Date endDate, String message) {
        return jdbcTemplate.queryForObject(messageIsExit, new Object[]{userId, eventId, bmw, endDate, message}, Integer.class);
    }
}